import pymysql
class DBTools():

    @classmethod
    def __create_conn(self):
        # 创建链接
        conn = pymysql.connect(
            host='192.168.246.130',
            port=3306,
            user='root',
            password='iTest_2022_mysql',
            database='test_db',
            charset='utf8'
        )
        return conn

    # 查询
    @classmethod
    def query_one(self, sql):
        my_conn = None
        my_cursor = None
        try:
            my_conn = DBTools.__create_conn()
            my_cursor = my_conn.cursor()
            my_cursor.execute(sql)
            res = my_cursor.fetchone()
        except Exception as e:
            print("查询失败: ", e)
        finally:
            # 关闭游标
            my_cursor.close()

            # 关闭连接
            my_conn.close()
            return res

    # 增删改
    @classmethod
    def db_uid(self, sql):
        my_conn = None
        my_cursor = None
        try:
            my_conn = DBTools.__create_conn()
            my_cursor = my_conn.cursor()
            my_cursor.execute(sql)
            print('Affected rows: ', my_conn.affected_rows())
            #     提交事务
            my_conn.commit()
        except Exception as e:
            print("增删改失败: ", e)
            #     回滚事务
            my_conn.rollback()
        finally:
            # 关闭游标
            my_cursor.close()

            # 关闭连接
            my_conn.close()


if __name__ == '__main__':
    result = DBTools.query_one('select * from t_book;')
    print('查询结果: ', result)

    DBTools.db_uid('update t_book set `read` = 100 where id = 3;')